﻿using System;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using System.IO;

using Microsoft.Win32;
using Microsoft.SqlServer.Management.Smo;

using P2.Cuberry.Framework.Helper;

namespace P2.Cuberry.Modules.Framework.ModuleManager.BusinessEntities
{
    public class XMLFileProcessor
    {
        const string KEY_TABLE_CREATE = "Create";
        const string KEY_TABLE_ALTER = "Alter";
        const string KEY_TABLE_INSERT_DATA = "InsertData";
        const string KEY_TABLE_UPDATE_DATA = "UpdateData";

        readonly string TABLE_CREATION;
        readonly string TABLE_UPDATE;
        readonly string SP_CREATION;
        readonly string VIEW_CREATION;
        readonly string FUNCTION_CREATION;
        readonly string DATA_INSERT;
        readonly string DATA_UPDATION;
        readonly string KEEP_CURRENT;
        readonly string REPLACE;

        public XMLFileProcessor(string connectionString)
        {
            TABLE_CREATION = P2Translator.GetResource("TableCreation"); //"Table Creation";
            TABLE_UPDATE = P2Translator.GetResource("TableUpdate"); //"Table Update";
            SP_CREATION = P2Translator.GetResource("SPCreation"); //"SP Creation";
            VIEW_CREATION = P2Translator.GetResource("ViewCreation"); //"View Creation";
            FUNCTION_CREATION = P2Translator.GetResource("FunctionCreation"); //"Function Creation";
            DATA_INSERT = P2Translator.GetResource("DataInsertion"); //"Data Insertion";
            DATA_UPDATION = P2Translator.GetResource("DataUpdation"); //"Data Updation";
            KEEP_CURRENT = P2Translator.GetResource("KeepCurrent"); //"Keep Current";
            REPLACE = P2Translator.GetResource("Replace");//"Replace";

            ConnectionString = connectionString;
            this.ParseConnectionString();
        }

        private ObservableCollection<ModulesImport> ImportList { get; set; }

        public string ConnectionString { get; set; }
        public string ServerName { get; set; }
        public string DBName { get; set; }
        public string Uid { get; set; }
        public string Pwd { get; set; }


        public void ProcessCreateXMLFile(string fileName, ObservableCollection<ModulesImport> importList)
        {
            if (!File.Exists(fileName))
                return;

            this.ImportList = importList;
            XPathDocument xpathDocument = new XPathDocument(fileName);
            XPathNavigator xpathNavigator = xpathDocument.CreateNavigator();

            ProcessCreateTableSection(xpathNavigator);
            ProcessAlterTableSection(xpathNavigator);
            ProcessViewSection(xpathNavigator);
            ProcessStoredProcSection(xpathNavigator);
            ProcessFunctionSection(xpathNavigator);
        }

        public void ProcessUpdateXMLFile(string fileName, ObservableCollection<ModulesImport> importList)
        {
            if (!File.Exists(fileName))
                return;

            XPathDocument xpathDocument = new XPathDocument(fileName);
            XPathNavigator xpathNavigator = xpathDocument.CreateNavigator();

            ProcessInsertDataSection(xpathNavigator);
            ProcessUpdateDataSection(xpathNavigator);
        }

        private void ProcessCreateTableSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator xpathCreateTableNodeIterator = xpathNavigator.Select("Schema//Tables//Create//Table");

                if (xpathCreateTableNodeIterator.Count == 0)
                    return;

                Dictionary<string, List<TableSchema>> tableCollection = new Dictionary<string, List<TableSchema>>();
                Dictionary<string, List<string>> primaryKeyList = new Dictionary<string, List<string>>();
                Dictionary<string, List<ForeignKeyConstraint>> foreignKeyList = new Dictionary<string, List<ForeignKeyConstraint>>();
                Dictionary<string, List<DefaultKeyConstraint>> defaultKeyList = new Dictionary<string, List<DefaultKeyConstraint>>();

                //Iterating on <Table> node
                while (xpathCreateTableNodeIterator.MoveNext())
                {
                    var tableName = xpathCreateTableNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(tableName))
                    {
                        throw new MissingMemberException("Table name was not provided in create section of Create xml file.");
                    }
                    if (!xpathCreateTableNodeIterator.Current.HasChildren)
                        continue;

                    XPathNodeIterator xpathNodeIteratorOfTable = xpathCreateTableNodeIterator.Current.SelectChildren(XPathNodeType.All);
                    //Iterating on children of <Table node> i.e. <Columns>, <Constraints>
                    while (xpathNodeIteratorOfTable.MoveNext())
                    {
                        if (xpathNodeIteratorOfTable.Current.LocalName.Trim().ToUpper() == "COLUMNS")
                        {
                            XPathNodeIterator columnsChildrenIterator = xpathNodeIteratorOfTable.Current.SelectChildren(XPathNodeType.All);

                            if (columnsChildrenIterator.Count > 0)
                            {
                                //Iterating on actual columns those are inside <columns> tag
                                while (columnsChildrenIterator.MoveNext())
                                {
                                    var columnName = columnsChildrenIterator.Current.LocalName;
                                    var columnType = columnsChildrenIterator.Current.GetAttribute("Type", "");
                                    var allowNull = columnsChildrenIterator.Current.GetAttribute("AllowNull", "");
                                    var columnSize = columnsChildrenIterator.Current.GetAttribute("Size", "");

                                    if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(columnType))
                                        throw new MissingMemberException("Column name or type was not provided in create section of Create XML file.");

                                    if (!tableCollection.ContainsKey(tableName))
                                    {
                                        List<TableSchema> columns = new List<TableSchema>();
                                        tableCollection.Add(tableName, columns);
                                    }

                                    TableSchema tableSchema = new TableSchema();
                                    tableSchema.ColumnName = columnName;
                                    tableSchema.ColumnType = columnType;

                                    if (!string.IsNullOrEmpty(allowNull))
                                    {
                                        tableSchema.AllowDBNull = allowNull.Trim() == "1" ? true : false;
                                    }

                                    if (!string.IsNullOrEmpty(columnSize))
                                    {
                                        int size = 0;
                                        if (int.TryParse(columnSize, out size))
                                        {
                                            tableSchema.ColumnSize = size;
                                        }
                                    }

                                    tableCollection[tableName].Add(tableSchema);
                                }
                            }
                        }
                        else if (xpathNodeIteratorOfTable.Current.LocalName.Trim().ToUpper() == "CONSTRAINTS")
                        {
                            XPathNodeIterator constraintsChildrenIterator = xpathNodeIteratorOfTable.Current.SelectChildren(XPathNodeType.All);
                            
                            if (!primaryKeyList.ContainsKey(tableName))
                                primaryKeyList.Add(tableName, new List<string>());

                            if (!foreignKeyList.ContainsKey(tableName))
                                foreignKeyList.Add(tableName, new List<ForeignKeyConstraint>());

                            if (!defaultKeyList.ContainsKey(tableName))
                                defaultKeyList.Add(tableName, new List<DefaultKeyConstraint>());

                            if (constraintsChildrenIterator.Count > 0)
                            {
                                //Iterating on childer of <Constraints> node i.e. <PrimaryKey>, <ForeignKey> etc.
                                while (constraintsChildrenIterator.MoveNext())
                                {
                                    if (constraintsChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYKEY")
                                    {
                                        if (constraintsChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator primaryKeyChildrenIterator = constraintsChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (primaryKeyChildrenIterator.Count > 0)
                                            {
                                                // Iterating on actual priamry key column i.e children of <priamrykey> tag.
                                                while (primaryKeyChildrenIterator.MoveNext())
                                                {
                                                    if (!string.IsNullOrEmpty(primaryKeyChildrenIterator.Current.LocalName))
                                                        primaryKeyList[tableName].Add(primaryKeyChildrenIterator.Current.LocalName.Trim());
                                                }
                                            }
                                        }
                                    }
                                    else if (constraintsChildrenIterator.Current.LocalName.Trim().ToUpper() == "DEFAULT")
                                    {
                                        if (constraintsChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator defaultKeyChildrenIterator = constraintsChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (defaultKeyChildrenIterator.Count > 0)
                                            {
                                                // Iterating of <defaultkey> node's children
                                                while (defaultKeyChildrenIterator.MoveNext())
                                                {
                                                    string defaultConstraintName = defaultKeyChildrenIterator.Current.GetAttribute("Name", "");
                                                    string defaultConstraintValue = defaultKeyChildrenIterator.Current.GetAttribute("Value", "");

                                                    if (string.IsNullOrEmpty(defaultConstraintName) || string.IsNullOrEmpty(defaultConstraintValue))
                                                    {
                                                        throw new MissingFieldException("Data is missing in Default section of ceate table xml");
                                                    }

                                                    defaultKeyList[tableName].Add(new DefaultKeyConstraint() { Name = defaultConstraintName, Value = defaultConstraintValue });
                                                }
                                            }
                                        }
                                    }
                                    else if (constraintsChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNKEY")
                                    {
                                        if (constraintsChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator foreignKeyChildrenIterator = constraintsChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (foreignKeyChildrenIterator.Count > 0)
                                            {
                                                ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint();

                                                while (foreignKeyChildrenIterator.MoveNext())
                                                {
                                                    if (foreignKeyChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYTABLE")
                                                    {
                                                        foreignKeyConstraint.PrimaryKeyTable = foreignKeyChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYTABLECOLUMN")
                                                    {
                                                        foreignKeyConstraint.PrimaryKeyColumn = foreignKeyChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNTABLE")
                                                    {
                                                        foreignKeyConstraint.ForeignKeyTable = foreignKeyChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNTABLECOLUMN")
                                                    {
                                                        foreignKeyConstraint.ForeignKeyColumn = foreignKeyChildrenIterator.Current.Value;
                                                    }
                                                }
                                                if (string.IsNullOrEmpty(foreignKeyConstraint.PrimaryKeyTable) || string.IsNullOrEmpty(foreignKeyConstraint.PrimaryKeyColumn)
                                                        || string.IsNullOrEmpty(foreignKeyConstraint.ForeignKeyTable) || string.IsNullOrEmpty(foreignKeyConstraint.ForeignKeyColumn))
                                                {
                                                    throw new MissingFieldException("Data is missing in ForeignKey section of ceate table xml");
                                                }

                                                foreignKeyList[tableName].Add(foreignKeyConstraint);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                // Add tables to database.

                AddTablesToDatabase(tableCollection,primaryKeyList, foreignKeyList, defaultKeyList);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AddTablesToDatabase(IDictionary<string, List<TableSchema>> tables, Dictionary<string, List<string>> primaryKeys,
                                        Dictionary<string, List<ForeignKeyConstraint>> foreignKeys, Dictionary<string, List<DefaultKeyConstraint>> defaultKeys)
        {
            if (tables.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            foreach (var table in tables)
            {
                ModulesImport modulesImport = ImportList.Where(e => e.ItemKey == table.Key.Trim() + KEY_TABLE_CREATE).FirstOrDefault();

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                    continue;

                modulesImport.Process = P2Translator.GetResource("TableCreating");

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == REPLACE)
                {
                    if (this.IsThisTableAlreadyExists(dataBase, table.Key.Trim()))
                    {
                        modulesImport.Process = modulesImport.Process + P2Translator.GetResource("TableBacking");
                        dataBase.Tables[table.Key.Trim()].Rename(table.Key.Trim() + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", "")); 
                    }
                }

                Table smoTable = new Table(dataBase, table.Key);

                foreach (var tableSchema in table.Value)
                {
                    DataType dataType = GetMeSQLDataType(tableSchema.ColumnType, tableSchema.ColumnSize);
                    Column column = new Column(smoTable, tableSchema.ColumnName, dataType);
                    column.Nullable = tableSchema.AllowDBNull;
                    smoTable.Columns.Add(column);
                }
                smoTable.Create();
                modulesImport.Process = modulesImport.Process + P2Translator.GetResource("TableCreated");
            }

            int counter = 1;
            foreach (var primarykey in primaryKeys)
            {
                if (primarykey.Value.Count == 0)
                    continue;

                ModulesImport modulesImport = ImportList.Where(e => e.ItemKey == primarykey.Key.Trim() + KEY_TABLE_CREATE).FirstOrDefault();
                if (modulesImport != null)
                {
                    if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                        continue;
                }

                Table smoTableForPK = dataBase.Tables[primarykey.Key];
                if (smoTableForPK == null)
                    continue;

                AddPrimaryKeyToTable(smoTableForPK, primarykey.Value, counter);
                counter += counter;
            }

            counter = 1;

            foreach (var foreignKey in foreignKeys)
            {
                ModulesImport modulesImport = ImportList.Where(e => e.ItemKey == foreignKey.Key.Trim() + KEY_TABLE_CREATE).FirstOrDefault();
                if (modulesImport != null)
                {
                    if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                        continue;
                }

                if (foreignKey.Value.Count == 0)
                    continue;

                Table smoTableForFK = dataBase.Tables[foreignKey.Key];
                if (smoTableForFK == null)
                    continue;

                AddForeignKeysToTable(smoTableForFK, foreignKey.Value, counter);
                counter += counter;
            }

            counter = 1;
            foreach (var defaultKey in defaultKeys)
            {
                if (defaultKey.Value.Count == 0)
                    continue;

                ModulesImport modulesImport = ImportList.Where(e => e.ItemKey == defaultKey.Key.Trim() + KEY_TABLE_CREATE).FirstOrDefault();
                if (modulesImport != null)
                {
                    if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                        continue;
                }

                Table smoTableForFK = dataBase.Tables[defaultKey.Key];
                if (smoTableForFK == null)
                    continue;

                AddDefaultConstraintToTable(smoTableForFK, defaultKey.Value, counter);
                counter += counter;
            }
        }

        private bool IsThisTableAlreadyExists(Database dataBase, string tableName)
        {
            return dataBase.Tables[tableName] == null ? false : true;
        }

        private void AddPrimaryKeyToTable(Table smoTable, List<string> columnList, int counter)
        {
            var indexName = smoTable.Name + "_PK" + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", "");

            if (smoTable.Indexes[indexName] != null)
                smoTable.Indexes[indexName].Drop();

            Index smoPrimaryKeyIndex = new Index(smoTable, indexName);

            columnList.ForEach(e => smoPrimaryKeyIndex.IndexedColumns.Add(new IndexedColumn(smoPrimaryKeyIndex, e)));
            smoPrimaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
            smoPrimaryKeyIndex.Create();
            smoTable.Alter();
        }

        private void AddForeignKeysToTable(Table smoTable, List<ForeignKeyConstraint> foreignKeys, int mainCounter)
        {
            int localCounter = 1;
            foreach (var foreignKeyConstraint in foreignKeys)
            {
                var foreignKeyName = smoTable.Name + "_" + foreignKeyConstraint.PrimaryKeyColumn + "_" + foreignKeyConstraint.ForeignKeyColumn
                    + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", "");

                if (smoTable.ForeignKeys[foreignKeyName] != null)
                    smoTable.ForeignKeys[foreignKeyName].Drop();

                ForeignKey foreignKey = new ForeignKey(smoTable, foreignKeyName);
                
                ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(foreignKey, foreignKeyConstraint.ForeignKeyColumn, foreignKeyConstraint.PrimaryKeyColumn);
                foreignKey.Columns.Add(foreignKeyColumn);
                foreignKey.ReferencedTable = foreignKeyConstraint.PrimaryKeyTable;
                foreignKey.Create();
                localCounter += localCounter;
            }
            smoTable.Alter();
        }

        private void AddDefaultConstraintToTable(Table smoTable, List<DefaultKeyConstraint> defaultConstraints, int mainCounter)
        {
            int localCounter = 1;
            foreach (var defaultKeyConstraint in defaultConstraints)
            {
                Column column = smoTable.Columns[defaultKeyConstraint.Name];
                if (column == null)
                    continue;

                var defaultConstraintName = smoTable.Name + "_" + defaultKeyConstraint.Name + "_"
                    + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", "");

                column.AddDefaultConstraint(defaultConstraintName);
                column.DefaultConstraint.Text = defaultKeyConstraint.Value;
                column.Alter();
                localCounter += localCounter;
            }
            smoTable.Alter();
        }

        private DataType GetMeSQLDataType(string dataType, int size)
        {
            DataType smoDataType = null;

            switch (dataType.Trim().ToUpper())
            {
                case "INT":
                    {
                        smoDataType = new DataType(SqlDataType.Int);
                        break;
                    }
                case "TIMESTAMP": // TS Bugfix create timestamp (1.0.1.1)
                    {
                        smoDataType = new DataType(SqlDataType.Timestamp);
                        break;
                    }
                case "BIGINT":
                    {
                        smoDataType = new DataType(SqlDataType.BigInt);
                        break;
                    }
                case "DECIMAL":
                    {
                        smoDataType = new DataType(SqlDataType.Decimal);
                        break;
                    }
                case "FLOAT":
                    {
                        smoDataType = new DataType(SqlDataType.Float);
                        break;
                    }
                case "MONEY":
                    {
                        smoDataType = new DataType(SqlDataType.Money);
                        break;
                    }
                case "SMALLINT":
                    {
                        smoDataType = new DataType(SqlDataType.SmallInt);
                        break;
                    }
                case "TINYINT":
                    {
                        smoDataType = new DataType(SqlDataType.TinyInt);
                        break;
                    }
                case "VARCHAR":
                    {
                        if (size>0)
                            smoDataType = new DataType(SqlDataType.VarBinary, size);
                        else
                            smoDataType = new DataType(SqlDataType.VarBinary);

                        break;
                    }
                case "NVARCHAR":
                    {
                        if (size > 0)
                            smoDataType = new DataType(SqlDataType.NVarChar, size);
                        else
                            smoDataType = new DataType(SqlDataType.NVarChar);

                        break;
                    }
                case "CHAR":
                    {
                        if (size > 0)
                            smoDataType = new DataType(SqlDataType.Char, size);
                        else
                            smoDataType = new DataType(SqlDataType.Char);

                        break;
                    }
                case "NCHAR":
                    {
                        if (size > 0)
                            smoDataType = new DataType(SqlDataType.NChar, size);
                        else
                            smoDataType = new DataType(SqlDataType.NChar);

                        break;
                    }
                case "TEXT":
                    {
                        smoDataType = new DataType(SqlDataType.Text);
                        break;
                    }
                case "NTEXT":
                    {
                        smoDataType = new DataType(SqlDataType.NText);
                        break;
                    }
                case "DATE":
                    {
                        smoDataType = new DataType(SqlDataType.Date);
                        break;
                    }
                case "DATETIME":
                    {
                        smoDataType = new DataType(SqlDataType.DateTime);
                        break;
                    }
                case "BIT":
                    {
                        smoDataType = new DataType(SqlDataType.Bit);
                        break;
                    }
            }
            return smoDataType;

        }

        private void ParseConnectionString()
        {
            IEnumerable<string> conenctionStringItems = this.ConnectionString.Split(';').Select(x => x.Trim());

            foreach (var item in conenctionStringItems)
            {
                if (item.Contains("Data Source"))
                {
                    if (item.IndexOf("=") != -1)
                        this.ServerName = item.Substring(item.IndexOf("=") + 1);
                }
                else if (item.Contains("Initial Catalog"))
                {
                    if (item.IndexOf("=") != -1)
                        this.DBName = item.Substring(item.IndexOf("=") + 1);
                }
                else if (item.Contains("uid"))
                {
                    if (item.IndexOf("=") != -1)
                        this.Uid = item.Substring(item.IndexOf("=") + 1);
                }
                else if (item.Contains("pwd"))
                {
                    if (item.IndexOf("=") != -1)
                        this.Pwd = item.Substring(item.IndexOf("=") + 1);
                }
            } 
        }

        private void ProcessAlterTableSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator xpathAlterTableNodeIterator = xpathNavigator.Select("Schema//Tables//Alter//Table");

                if (xpathAlterTableNodeIterator.Count == 0)
                    return;

                Dictionary<string, List<TableSchema>> tableCollection = new Dictionary<string, List<TableSchema>>();
                Dictionary<string, List<string>> primaryKeyList = new Dictionary<string, List<string>>();
                Dictionary<string, List<ForeignKeyConstraint>> foreignKeyList = new Dictionary<string, List<ForeignKeyConstraint>>();
                Dictionary<string, List<DefaultKeyConstraint>> defaultKeyList = new Dictionary<string, List<DefaultKeyConstraint>>();

                //Iterating on <Table> node
                while (xpathAlterTableNodeIterator.MoveNext())
                {
                    var tableName = xpathAlterTableNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(tableName))
                    {
                        throw new MissingMemberException("Table name was not provided in alter section of Create xml file.");
                    }
                    if (!xpathAlterTableNodeIterator.Current.HasChildren)
                        continue;

                    XPathNodeIterator xpathTableNodeChildrenIterator = xpathAlterTableNodeIterator.Current.SelectChildren(XPathNodeType.All);
                    //Iterating on children on <Table> node i.e. <columns>, <Constraints> etc.
                    while (xpathTableNodeChildrenIterator.MoveNext())
                    {
                        if (xpathTableNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "COLUMNS")
                        {
                            XPathNodeIterator xpathColumnsNodeChildrenIterator = xpathTableNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);

                            if (xpathColumnsNodeChildrenIterator.Count > 0)
                            {
                                while (xpathColumnsNodeChildrenIterator.MoveNext())
                                {
                                    var columnName = xpathColumnsNodeChildrenIterator.Current.LocalName;
                                    var columnType = xpathColumnsNodeChildrenIterator.Current.GetAttribute("Type", "");
                                    var allowNull = xpathColumnsNodeChildrenIterator.Current.GetAttribute("AllowNull", "");
                                    var columnSize = xpathColumnsNodeChildrenIterator.Current.GetAttribute("Size", "");

                                    if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(columnType))
                                        throw new ArgumentNullException("Column name or type was not provided in alter section of Create table xml file.");

                                    if (!tableCollection.ContainsKey(tableName))
                                    {
                                        List<TableSchema> columns = new List<TableSchema>();
                                        tableCollection.Add(tableName, columns);
                                    }

                                    TableSchema tableSchema = new TableSchema();
                                    tableSchema.ColumnName = columnName;
                                    tableSchema.ColumnType = columnType;

                                    if (!string.IsNullOrEmpty(allowNull))
                                    {
                                        tableSchema.AllowDBNull = allowNull.Trim() == "1" ? true : false;
                                    }

                                    if (!string.IsNullOrEmpty(columnSize))
                                    {
                                        int size = 0;
                                        if (int.TryParse(columnSize, out size))
                                        {
                                            tableSchema.ColumnSize = size;
                                        }
                                    }
                                    tableCollection[tableName].Add(tableSchema);
                                }
                            }
                        }
                        else if (xpathTableNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "CONSTRAINTS")
                        {
                            XPathNodeIterator constraintsNodeChildrenIterator = xpathTableNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);

                            if (!primaryKeyList.ContainsKey(tableName))
                                primaryKeyList.Add(tableName, new List<string>());

                            if (!foreignKeyList.ContainsKey(tableName))
                                foreignKeyList.Add(tableName, new List<ForeignKeyConstraint>());

                            if (!defaultKeyList.ContainsKey(tableName))
                                defaultKeyList.Add(tableName, new List<DefaultKeyConstraint>());

                            if (constraintsNodeChildrenIterator.Count > 0)
                            {
                                while (constraintsNodeChildrenIterator.MoveNext())
                                {
                                    if (constraintsNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYKEY")
                                    {
                                        if (constraintsNodeChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator primaryKeyChildrenIterator = constraintsNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (primaryKeyChildrenIterator.Count > 0)
                                            {
                                                while (primaryKeyChildrenIterator.MoveNext())
                                                {
                                                    if (!string.IsNullOrEmpty(primaryKeyChildrenIterator.Current.LocalName))
                                                        primaryKeyList[tableName].Add(primaryKeyChildrenIterator.Current.LocalName.Trim());
                                                }
                                            }
                                        }
                                    }
                                    else if (constraintsNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "DEFAULT")
                                    {
                                        if (constraintsNodeChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator defaultNodeChildrenIterator = constraintsNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (defaultNodeChildrenIterator.Count > 0)
                                            {
                                                while (defaultNodeChildrenIterator.MoveNext())
                                                {
                                                    string defaultConstraintName = defaultNodeChildrenIterator.Current.GetAttribute("Name", "");
                                                    string defaultConstraintValue = defaultNodeChildrenIterator.Current.GetAttribute("Value", "");

                                                    if (string.IsNullOrEmpty(defaultConstraintName) || string.IsNullOrEmpty(defaultConstraintValue))
                                                    {
                                                        throw new MissingFieldException("Data is missing in Default section of alter table section of Create Table xml file.");
                                                    }

                                                    defaultKeyList[tableName].Add(new DefaultKeyConstraint() { Name = defaultConstraintName, Value = defaultConstraintValue });
                                                }
                                            }
                                        }
                                    }
                                    else if (constraintsNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNKEY")
                                    {
                                        if (constraintsNodeChildrenIterator.Current.HasChildren)
                                        {
                                            XPathNodeIterator foreignKeyNodeChildrenIterator = constraintsNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);
                                            if (foreignKeyNodeChildrenIterator.Count > 0)
                                            {
                                                ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint();

                                                while (foreignKeyNodeChildrenIterator.MoveNext())
                                                {
                                                    if (foreignKeyNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYTABLE")
                                                    {
                                                        foreignKeyConstraint.PrimaryKeyTable = foreignKeyNodeChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "PRIMARYTABLECOLUMN")
                                                    {
                                                        foreignKeyConstraint.PrimaryKeyColumn = foreignKeyNodeChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNTABLE")
                                                    {
                                                        foreignKeyConstraint.ForeignKeyTable = foreignKeyNodeChildrenIterator.Current.Value;
                                                    }
                                                    else if (foreignKeyNodeChildrenIterator.Current.LocalName.Trim().ToUpper() == "FOREIGNTABLECOLUMN")
                                                    {
                                                        foreignKeyConstraint.ForeignKeyColumn = foreignKeyNodeChildrenIterator.Current.Value;
                                                    }
                                                }
                                                if (string.IsNullOrEmpty(foreignKeyConstraint.PrimaryKeyTable) || string.IsNullOrEmpty(foreignKeyConstraint.PrimaryKeyColumn)
                                                    || string.IsNullOrEmpty(foreignKeyConstraint.ForeignKeyTable) || string.IsNullOrEmpty(foreignKeyConstraint.ForeignKeyColumn))
                                                {
                                                    throw new MissingFieldException("Data is missing in ForeignKey section of alter table section of Ceate Table xml file.");
                                                }
                                                foreignKeyList[tableName].Add(foreignKeyConstraint);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                // Alter tables to database.
                AlterTablesInDatabase(tableCollection,primaryKeyList, foreignKeyList, defaultKeyList);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AlterTablesInDatabase(IDictionary<string, List<TableSchema>> tables, Dictionary<string, List<string>> primaryKeys,
                                Dictionary<string, List<ForeignKeyConstraint>> foreignKeys, Dictionary<string, List<DefaultKeyConstraint>> defaultKeys)
        {
            if (tables.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            foreach (var table in tables)
            {

                ModulesImport modulesImport = ImportList.Where(e => e.ItemKey == table.Key.Trim() + KEY_TABLE_ALTER).FirstOrDefault();

                if (modulesImport.ItemValidationStatus == ImportItemStatus.TableDoesNotExists)
                    continue;

                Table smoTable = dataBase.Tables[table.Key];

                if (smoTable == null)
                {
                    //throw new MissingObjectException(table.Key + " table does not exists in cuberry databse");
                    modulesImport.Process = modulesImport.Process + P2Translator.GetResource("TableDoesNotExists");
                    continue;
                }

                modulesImport.Process = P2Translator.GetResource("TableUpdating");

                foreach (var tableSchema in table.Value)
                {
                    if (smoTable.Columns[tableSchema.ColumnName] != null)
                    {
                        modulesImport.Process = modulesImport.Process + string.Format(P2Translator.GetResource("ColumnAlreadyExists"), tableSchema.ColumnName, smoTable.Name) ;
                        continue;
                    }

                    DataType dataType = GetMeSQLDataType(tableSchema.ColumnType, tableSchema.ColumnSize);
                    Column column = new Column(smoTable, tableSchema.ColumnName, dataType);
                    column.Nullable = tableSchema.AllowDBNull;
                    smoTable.Columns.Add(column);                    
                }
                smoTable.Alter();
                modulesImport.Process = modulesImport.Process + P2Translator.GetResource("TableUpdated");
            }

            int counter = 1;
            foreach (var primarykey in primaryKeys)
            {
                if (primarykey.Value.Count == 0)
                    continue;

                Table smoTableForPK = dataBase.Tables[primarykey.Key];
                if (smoTableForPK == null)
                    continue;

                AddPrimaryKeyToTable(smoTableForPK, primarykey.Value, counter);
                counter += counter;
            }

            counter = 1;
            foreach (var foreignKey in foreignKeys)
            {
                if (foreignKey.Value.Count == 0)
                    continue;

                Table smoTableForFK = dataBase.Tables[foreignKey.Key];
                if (smoTableForFK == null)
                    continue;

                AddForeignKeysToTable(smoTableForFK, foreignKey.Value, counter);
                counter += counter;
            }

            counter = 1;
            foreach (var defaultKey in defaultKeys)
            {
                if (defaultKey.Value.Count == 0)
                    continue;

                Table smoTableForFK = dataBase.Tables[defaultKey.Key];
                if (smoTableForFK == null)
                    continue;

                AddDefaultConstraintToTable(smoTableForFK, defaultKey.Value, counter);
                counter += counter;
            }
        }

        private void ProcessViewSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator xpathViewNodeIterator = xpathNavigator.Select("Schema//Views//View");

                if (xpathViewNodeIterator.Count == 0)
                    return;

                Dictionary<string, string> viewsDictionary = new Dictionary<string, string>();

                //Iterating on <view> node
                while (xpathViewNodeIterator.MoveNext())
                {
                    var viewName = xpathViewNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(viewName))
                        throw new MissingMemberException("View name was not provided in view node of Create Xml file.");

                    if (!xpathViewNodeIterator.Current.HasChildren)
                        throw new MissingMemberException("View body was not provided in view node of Create Xml file.");

                    // Here moving to <Body> tag of views
                    xpathViewNodeIterator.Current.MoveToFirstChild();
                    var viewBody = xpathViewNodeIterator.Current.Value;

                    if (string.IsNullOrEmpty(viewBody))
                        throw new MissingMemberException("View body was not provided in view node of Create Xml file.");

                    if (!viewsDictionary.ContainsKey(viewName))
                        viewsDictionary.Add(viewName, viewBody);
                }

                AddViewsInDatabase(viewsDictionary);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AddViewsInDatabase(Dictionary<string, string> viewDictionary)
        {
            if (viewDictionary.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            if (dataBase == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            View view;

            foreach (var viewItem in viewDictionary)
            {
                ModulesImport modulesImport = this.ImportList.Where(e => e.ItemKey == viewItem.Key.Trim()).FirstOrDefault();

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                    continue;

                modulesImport.Process = P2Translator.GetResource("ViewCreating");

                if (modulesImport.ItemValidationStatus==ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken==REPLACE)
                {
                    View viewToRename = dataBase.Views[viewItem.Key.Trim()];
                    if (viewToRename != null)
                    {
                        modulesImport.Process = modulesImport.Process + P2Translator.GetResource("ViewBacking");
                        viewToRename.Rename(viewToRename.Name + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", "")); 
                    }
                }
                
                view = new View(dataBase, viewItem.Key);
                view.TextHeader = "CREATE VIEW [dbo].[" + viewItem.Key + "] AS";
                view.TextBody = viewItem.Value;
                view.Create();
                modulesImport.Process = modulesImport.Process + P2Translator.GetResource("ViewCreated");
            }
        }

        private void ProcessStoredProcSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator xpathStoredProcNodeIterator = xpathNavigator.Select("Schema//StoredProcs//StoredProc");

                if (xpathStoredProcNodeIterator.Count == 0)
                    return;

                Dictionary<string, StoredProcSchema> storedProcsDictionary = new Dictionary<string, StoredProcSchema>();

                //Iterating of <StoredProc> nodes.
                while (xpathStoredProcNodeIterator.MoveNext())
                {
                    var storedProcName = xpathStoredProcNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(storedProcName))
                        throw new MissingMemberException("Stored procedure name was not provided in StoredProc section of Create Xml file.");

                    if (!xpathStoredProcNodeIterator.Current.HasChildren)
                        throw new MissingMemberException("Stored procedure's parameters or body was not provided in StoredProc node of Create Xml file.");

                    if (!storedProcsDictionary.ContainsKey(storedProcName))
                        storedProcsDictionary.Add(storedProcName, new StoredProcSchema());

                    // Here moving to <Parameteres> tag of stored proc
                    xpathStoredProcNodeIterator.Current.MoveToFirstChild();

                    if (xpathStoredProcNodeIterator.Current.HasChildren)
                    {
                        XPathNodeIterator parametersNodeChildrenIterator = xpathStoredProcNodeIterator.Current.SelectChildren(XPathNodeType.All);

                        //Itearting on real parameters i.e. children of <parameters> node.
                        while (parametersNodeChildrenIterator.MoveNext())
                        {
                            if (!parametersNodeChildrenIterator.Current.HasAttributes)
                                continue;

                            var paramName = parametersNodeChildrenIterator.Current.GetAttribute("Name", "");
                            var paramType = parametersNodeChildrenIterator.Current.GetAttribute("Type", "");
                            var paramSize = parametersNodeChildrenIterator.Current.GetAttribute("Size", "");
                            var paramOut = parametersNodeChildrenIterator.Current.GetAttribute("Out", "");

                            if (string.IsNullOrEmpty(paramName) || string.IsNullOrEmpty(paramType))
                                throw new MissingMemberException("Either parameter name or type was not provided in StoredProc node in Create XML file.");

                            ProcParameter storedProcParameter = new ProcParameter();

                            storedProcParameter.ParameterName = paramName;
                            storedProcParameter.ParameterType = paramType;
                            
                            if (!string.IsNullOrEmpty(paramOut))
                                storedProcParameter.IsOutputParameter = paramOut.Trim().ToUpper() == "TRUE" ? true: false;

                            int size = 0;
                            if (int.TryParse(paramSize, out size))
                                storedProcParameter.ParameterSize = size;

                            storedProcsDictionary[storedProcName].Parameters.Add(storedProcParameter);
                        }
                    }

                    // Here moving to <Body> tag of stored proc
                    xpathStoredProcNodeIterator.Current.MoveToNext();

                    var storedProcBody = xpathStoredProcNodeIterator.Current.Value;

                    if (string.IsNullOrEmpty(storedProcBody))
                        throw new MissingMemberException("Stored procedure body was not provided in StoredProc node of Create Xml file.");

                    storedProcsDictionary[storedProcName].BodyText = storedProcBody;
                }

                AddStoredProceduresInDatabase(storedProcsDictionary);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AddStoredProceduresInDatabase(Dictionary<string, StoredProcSchema> storedProcDictionary)
        {
            if (storedProcDictionary.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            if (dataBase == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            StoredProcedure storedProcedure;

            foreach (var storedProcItem in storedProcDictionary)
            {

                ModulesImport modulesImport = this.ImportList.Where(e => e.ItemKey == storedProcItem.Key.Trim()).FirstOrDefault();

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                    continue;

                modulesImport.Process = P2Translator.GetResource("SPCreating");

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == REPLACE)
                {
                    StoredProcedure spToRename = dataBase.StoredProcedures[storedProcItem.Key.Trim()];
                    if (spToRename != null)
                    {
                        modulesImport.Process = modulesImport.Process + P2Translator.GetResource("SPBacking");
                        spToRename.Rename(spToRename.Name + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", ""));
                    }
                }

                storedProcedure = new StoredProcedure(dataBase, storedProcItem.Key);

                storedProcedure.TextMode = false;
                storedProcedure.AnsiNullsStatus=false;
                storedProcedure.QuotedIdentifierStatus = false;

                storedProcedure.TextBody = storedProcItem.Value.BodyText;

                if (storedProcItem.Value.Parameters.Count > 0)
                {
                    foreach (ProcParameter storedProcParameter in storedProcItem.Value.Parameters)
                    {
                        StoredProcedureParameter param = new StoredProcedureParameter(storedProcedure, storedProcParameter.ParameterName);
                        param.DataType = GetMeSQLDataType(storedProcParameter.ParameterType, storedProcParameter.ParameterSize);
                        param.IsOutputParameter = storedProcParameter.IsOutputParameter;

                        storedProcedure.Parameters.Add(param);
                    }
                }
                storedProcedure.Create();
                storedProcedure.QuotedIdentifierStatus = true;
                storedProcedure.Alter();
                modulesImport.Process = modulesImport.Process + P2Translator.GetResource("SPCreated");
            }
        }

        private void ProcessFunctionSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator functionsNodeChildrenIterator = xpathNavigator.Select("Schema//Functions//Function");

                if (functionsNodeChildrenIterator.Count == 0)
                    return;

                Dictionary<string, FunctionSchema> functionsDictionary = new Dictionary<string, FunctionSchema>();

                while (functionsNodeChildrenIterator.MoveNext())
                {
                    var functionName = functionsNodeChildrenIterator.Current.GetAttribute("Name", "");
                    var functionType = functionsNodeChildrenIterator.Current.GetAttribute("Type", "");
                    var functionReturnDataType = functionsNodeChildrenIterator.Current.GetAttribute("DataType", "");
                    var functionReturnDataTypeSize = functionsNodeChildrenIterator.Current.GetAttribute("Size", "");

                    if (string.IsNullOrEmpty(functionName) || string.IsNullOrEmpty(functionType) || string.IsNullOrEmpty(functionReturnDataType))
                        throw new MissingMemberException("Eitehr function name, type or datatype was not provided in Functions section of Create Xml file.");

                    if (!functionsNodeChildrenIterator.Current.HasChildren)
                        throw new MissingMemberException("Function's parameters or body was not provided in Functions node of Create Xml file.");

                    if (!functionsDictionary.ContainsKey(functionName))
                        functionsDictionary.Add(functionName, new FunctionSchema());

                    functionsDictionary[functionName].FunctionType = functionType;
                    functionsDictionary[functionName].FunctionReturnDataType = functionReturnDataType;

                    if (!string.IsNullOrEmpty(functionReturnDataTypeSize))
                    {
                        int size = 0;
                        if (int.TryParse(functionReturnDataTypeSize, out size))
                            functionsDictionary[functionName].FunctionReturnDataTypeSize = size;
                    }

                    // Here moving to <Parameteres> tag of function
                    functionsNodeChildrenIterator.Current.MoveToFirstChild();

                    if (functionsNodeChildrenIterator.Current.HasChildren)
                    {
                        XPathNodeIterator parametersNodeChildrenIterator = functionsNodeChildrenIterator.Current.SelectChildren(XPathNodeType.All);

                        //Iterating on real parameters i.e. children of <Parameters> node.
                        while (parametersNodeChildrenIterator.MoveNext())
                        {
                            if (!parametersNodeChildrenIterator.Current.HasAttributes)
                                continue;

                            var paramName = parametersNodeChildrenIterator.Current.GetAttribute("Name", "");
                            var paramType = parametersNodeChildrenIterator.Current.GetAttribute("Type", "");
                            var paramSize = parametersNodeChildrenIterator.Current.GetAttribute("Size", "");
                            var paramOut = parametersNodeChildrenIterator.Current.GetAttribute("Out", "");

                            if (string.IsNullOrEmpty(paramName) || string.IsNullOrEmpty(paramType))
                                throw new MissingMemberException("Either parameter name or type was not provided in Function node in Create XML file.");

                            ProcParameter procParameter = new ProcParameter();

                            procParameter.ParameterName = paramName;
                            procParameter.ParameterType = paramType;

                            if (!string.IsNullOrEmpty(paramOut))
                                procParameter.IsOutputParameter = paramOut.Trim().ToUpper() == "TRUE" ? true : false;

                            int size = 0;
                            if (int.TryParse(paramSize, out size))
                                procParameter.ParameterSize = size;

                            functionsDictionary[functionName].Parameters.Add(procParameter);
                        }
                    }

                    // Here moving to <Body> tag of Function
                    functionsNodeChildrenIterator.Current.MoveToNext();

                    var functionBody = functionsNodeChildrenIterator.Current.Value;

                    if (string.IsNullOrEmpty(functionBody))
                        throw new MissingMemberException("Function body was not provided in Function node of Create Xml file.");

                    functionsDictionary[functionName].BodyText = functionBody;
                }

                AddFunctionsInDatabase(functionsDictionary);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AddFunctionsInDatabase(Dictionary<string, FunctionSchema> functionDictionary)
        {
            if (functionDictionary.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            if (dataBase == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            UserDefinedFunction userDefinedFunction;

            foreach (var functionItem in functionDictionary)
            {
                ModulesImport modulesImport = this.ImportList.Where(e => e.ItemKey == functionItem.Key.Trim()).FirstOrDefault();

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == KEEP_CURRENT)
                    continue;

                modulesImport.Process = P2Translator.GetResource("FNCreating");

                if (modulesImport.ItemValidationStatus == ImportItemStatus.AlreadyExists && modulesImport.ActionToBeTaken == REPLACE)
                {
                    UserDefinedFunction functionToRename = dataBase.UserDefinedFunctions[functionItem.Key.Trim()];
                    if (functionToRename != null)
                    {
                        modulesImport.Process = modulesImport.Process + P2Translator.GetResource("FNBacking");
                        functionToRename.Rename(functionToRename.Name + DateTime.Now.ToShortDateString().Replace("/", "") + DateTime.Now.ToLongTimeString().Replace(":", "").Replace(" ", ""));
                    }
                }

                userDefinedFunction = new UserDefinedFunction(dataBase, functionItem.Key);

                userDefinedFunction.TextMode = false;
                userDefinedFunction.AnsiNullsStatus = false;
                userDefinedFunction.QuotedIdentifierStatus = false;
                userDefinedFunction.ImplementationType = ImplementationType.TransactSql;
                userDefinedFunction.ExecutionContext = ExecutionContext.Caller;

                userDefinedFunction.FunctionType = functionItem.Value.FunctionUserDefinedype;                
                userDefinedFunction.TextBody = functionItem.Value.BodyText;
                if (!string.IsNullOrEmpty(functionItem.Value.FunctionReturnDataType))
                {
                    DataType dataType = GetMeSQLDataType(functionItem.Value.FunctionReturnDataType, functionItem.Value.FunctionReturnDataTypeSize);
                    if (dataType != null)
                        userDefinedFunction.DataType = dataType;
                }

                if (functionItem.Value.Parameters.Count > 0)
                {
                    foreach (ProcParameter funcParameter in functionItem.Value.Parameters)
                    {
                        UserDefinedFunctionParameter param = new UserDefinedFunctionParameter(userDefinedFunction, funcParameter.ParameterName);
                        param.DataType = GetMeSQLDataType(funcParameter.ParameterType, funcParameter.ParameterSize);
                        userDefinedFunction.Parameters.Add(param);
                    }
                }
                userDefinedFunction.Create();
                modulesImport.Process = modulesImport.Process + P2Translator.GetResource("FNCreated");
            }
        }

        private void ProcessInsertDataSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator tableNodeIterator = xpathNavigator.Select("Data//Insert//Table");

                if (tableNodeIterator.Count == 0)
                    return;

                Dictionary<string, List<RowInfo>> dataCollection = new Dictionary<string, List<RowInfo>>();

                // here we are iterating on <Table> nodes
                while (tableNodeIterator.MoveNext())
                {
                    var tableName = tableNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(tableName))
                        throw new MissingMemberException("Table name was not provided in Insert section of Update Xml file.");

                    if (!tableNodeIterator.Current.HasChildren)
                        continue;

                    if (!dataCollection.ContainsKey(tableName))
                        dataCollection.Add(tableName, new List<RowInfo>());

                    // Retrieving all <row> nodes
                    XPathNodeIterator rowNodeIterator = tableNodeIterator.Current.SelectChildren(XPathNodeType.All);
                    
                    //Iterating on <row> nodes
                    while (rowNodeIterator.MoveNext())
                    {
                        if (!rowNodeIterator.Current.HasChildren)
                            continue;
                        //Retriving all column data
                        XPathNodeIterator rowNodeChildrenIterator = rowNodeIterator.Current.SelectChildren(XPathNodeType.All);

                        RowInfo rowInfo = new RowInfo();
                        //Iterating on all column data
                        while (rowNodeChildrenIterator.MoveNext())
                        {
                            var columnName = rowNodeChildrenIterator.Current.LocalName;
                            var columnValue = rowNodeChildrenIterator.Current.Value;
                            var columnValueType = rowNodeChildrenIterator.Current.GetAttribute("Type", "");

                            if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(columnValue) || string.IsNullOrEmpty(columnValueType))
                                throw new MissingMemberException("Column name, value or type was not provided in Row section of Update XML file.");

                            rowInfo.RowData.Add(new ColumnInfo(){ColumnName = columnName, ColumnValue = columnValue, ColumnValueTypeString = columnValueType});
                        }
                        dataCollection[tableName].Add(rowInfo);
                    }
                }
                AddDataInDatabase(dataCollection);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void ProcessUpdateDataSection(XPathNavigator xpathNavigator)
        {
            try
            {
                XPathNodeIterator tableNodeIterator = xpathNavigator.Select("Data//Update//Table");

                if (tableNodeIterator.Count == 0)
                    return;

                Dictionary<string, List<UpdateStatement>> updateCollection = new Dictionary<string, List<UpdateStatement>>();

                // here we are @ <Table> node
                while (tableNodeIterator.MoveNext())
                {
                    var tableName = tableNodeIterator.Current.GetAttribute("Name", "");

                    if (string.IsNullOrEmpty(tableName))
                        throw new MissingMemberException("Table name was not provided in Update section of Update Xml file.");

                    if (!tableNodeIterator.Current.HasChildren)
                        continue;

                    if (!updateCollection.ContainsKey(tableName))
                        updateCollection.Add(tableName, new List<UpdateStatement>());

                    // Here retrieve all <Statement> nodes
                    XPathNodeIterator statementNodeIterator = tableNodeIterator.Current.SelectChildren(XPathNodeType.All);

                    if (statementNodeIterator.Count > 0)
                    {
                        // here we are iterating all <Statement>'s children nodes i.e. <set> and <where>
                        while (statementNodeIterator.MoveNext())
                        {
                            if (!statementNodeIterator.Current.HasChildren)
                                continue;

                            // We are moving to <Set> node
                            statementNodeIterator.Current.MoveToFirstChild();

                            if (!statementNodeIterator.Current.HasChildren)
                                continue;

                            UpdateStatement updateStatement = new UpdateStatement();

                            //Retriving <Set> node
                            XPathNodeIterator setNodeChildrenIterator = statementNodeIterator.Current.SelectChildren(XPathNodeType.All);

                            if (setNodeChildrenIterator.Count > 0)
                            {
                                //Iterating on columns in Set section
                                while (setNodeChildrenIterator.MoveNext())
                                {
                                    var columnName = setNodeChildrenIterator.Current.LocalName;
                                    var columnValue = setNodeChildrenIterator.Current.Value;
                                    var columnValueType = setNodeChildrenIterator.Current.GetAttribute("Type", "");

                                    if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(columnValue) || string.IsNullOrEmpty(columnValueType))
                                        throw new MissingMemberException("Column information was not provided in Set section of Update XML file.");

                                    updateStatement.SetStatementValue.Add(new ColumnInfo() { ColumnName = columnName, ColumnValue = columnValue, ColumnValueTypeString = columnValueType });
                                }
                            }

                            //Moving to <Where> node
                            statementNodeIterator.Current.MoveToNext();

                            if (!statementNodeIterator.Current.HasChildren)
                                continue;

                            //Retriving <Where> node
                            XPathNodeIterator whereNodeChildrenIterator = statementNodeIterator.Current.SelectChildren(XPathNodeType.All);

                            if (whereNodeChildrenIterator.Count > 0)
                            {
                                //Iterating columns in where section
                                while (whereNodeChildrenIterator.MoveNext())
                                {
                                    var columnName = whereNodeChildrenIterator.Current.LocalName;
                                    var columnValue = whereNodeChildrenIterator.Current.Value;
                                    var columnValueType = whereNodeChildrenIterator.Current.GetAttribute("Type", "");

                                    if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(columnValue) || string.IsNullOrEmpty(columnValueType))
                                        throw new MissingMemberException("Column information was not provided in Where section of Update XML file.");

                                    updateStatement.WhereStatementValue.Add(new ColumnInfo() { ColumnName = columnName, ColumnValue = columnValue, ColumnValueTypeString = columnValueType });
                                }
                            }
                            updateCollection[tableName].Add(updateStatement);
                        }
                    }
                }
                UpdateDataInDatabase(updateCollection);
            }
            catch (Exception ex)
            {
                P2ExceptionHandler.LogException(ex, string.Format("{0}.{1}", this.GetType().Name, System.Reflection.MethodInfo.GetCurrentMethod().Name));
                throw;
            }
        }

        private void AddDataInDatabase(Dictionary<string, List<RowInfo>> rowCollection)
        {
            if (rowCollection.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            if (dataBase == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            foreach (var tableItem in rowCollection)
            {
                if (tableItem.Value.Count == 0)
                    continue;

                ModulesImport modulesImport = this.ImportList.Where(e => e.ItemKey == tableItem.Key.Trim() + KEY_TABLE_INSERT_DATA).FirstOrDefault();
                if (modulesImport.ItemValidationStatus == ImportItemStatus.TableDoesNotExists)
                {
                    modulesImport.Process = P2Translator.GetResource("TableDoesNotExists");
                    continue;
                }
                modulesImport.Process = P2Translator.GetResource("DataAdding");

                Table tb = dataBase.Tables[tableItem.Key];

                foreach (RowInfo rowInfo in tableItem.Value)
                {
                    // If no column then move to next row
                    if (rowInfo.RowData.Count == 0)
                        continue;

                    StringBuilder insertStatement = new StringBuilder();
                    insertStatement.Append(string.Format("Insert Into [{0}] (", tableItem.Key));

                    int columnCounter = 0;
                    foreach (ColumnInfo columnInfo in rowInfo.RowData)
                    {
                        if (columnCounter == 0)
                            insertStatement.Append("[" + columnInfo.ColumnName + "]");
                        else
                            insertStatement.Append(", [" + columnInfo.ColumnName + "]");

                        columnCounter += 1;
                    }
                    insertStatement.Append(") Values(");
                    columnCounter = 0;

                    foreach (ColumnInfo columnInfo in rowInfo.RowData)
                    {
                        if (columnCounter == 0)
                            insertStatement.Append(GetMeStatementForInsertDataInColumn(columnInfo));
                        else
                            insertStatement.Append(", " + GetMeStatementForInsertDataInColumn(columnInfo));

                        columnCounter += 1;
                    }
                    insertStatement.Append(")");
                    //Executing insert statement in database.
                    try
                    {
                        dataBase.ExecuteNonQuery(insertStatement.ToString());
                        modulesImport.Process = P2Translator.GetResource("DataAdded");
                    }
                    catch (Exception ex)
                    {
                        //Exception will come in case of key violation
                    }
                }
            }
        }

        private void UpdateDataInDatabase(Dictionary<string, List<UpdateStatement>> updateStatements)
        {
            if (updateStatements.Count == 0)
                return;

            Server server = new Server(this.ServerName);

            if (server.Databases[this.DBName] == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            Database dataBase = server.Databases[this.DBName];

            if (dataBase == null)
                throw new InvalidOperationException("Database does not exists on the server. Can not continue import module operations");

            foreach (var updateItem in updateStatements)
            {
                if (updateItem.Value.Count == 0)
                    continue;

                ModulesImport modulesImport = this.ImportList.Where(e => e.ItemKey == updateItem.Key.Trim() + KEY_TABLE_UPDATE_DATA).FirstOrDefault();
                if (modulesImport.ItemValidationStatus == ImportItemStatus.TableDoesNotExists)
                {
                    modulesImport.Process = P2Translator.GetResource("TableDoesNotExists");
                    continue;
                }
                modulesImport.Process = P2Translator.GetResource("DataUpdating");

                foreach (UpdateStatement updateStatement in updateItem.Value)
                {
                    // If no column then move to next row
                    if (updateStatement.SetStatementValue.Count == 0 || updateStatement.WhereStatementValue.Count == 0)
                        continue;

                    StringBuilder updateBodyText = new StringBuilder();
                    updateBodyText.Append(string.Format("Update [{0}] Set ", updateItem.Key));

                    int columnCounter = 0;
                    foreach (ColumnInfo columnInfo in updateStatement.SetStatementValue)
                    {
                        if (columnCounter == 0)
                            updateBodyText.Append(GetMeStatementForUpdateDataInColumn(columnInfo));
                        else
                            updateBodyText.Append(", " + GetMeStatementForUpdateDataInColumn(columnInfo));

                        columnCounter += 1;
                    }

                    columnCounter = 0;
                    updateBodyText.Append(" Where ");

                    foreach (ColumnInfo columnInfo in updateStatement.WhereStatementValue)
                    {
                        if (columnCounter == 0)
                            updateBodyText.Append(GetMeStatementForUpdateDataInColumn(columnInfo));
                        else
                            updateBodyText.Append(" And " + GetMeStatementForUpdateDataInColumn(columnInfo));

                        columnCounter += 1;
                    }
                    //Executing update statement in database.
                    try
                    {
                        dataBase.ExecuteNonQuery(updateBodyText.ToString());
                        modulesImport.Process = P2Translator.GetResource("DataUpdated");
                    }
                    catch (Exception ex)
                    {
                        // In case of key violation
                    }
                }
            }
        }

        private string GetMeStatementForUpdateDataInColumn(ColumnInfo columnInfo)
        {
            if (columnInfo.ColumnValueType == ColumnValueDataType.Numeric)
                return "[" + columnInfo.ColumnName + "] = " + columnInfo.ColumnValue;
            else if(columnInfo.ColumnValueType==ColumnValueDataType.Text)
                return "[" + columnInfo.ColumnName + "] = '" + columnInfo.ColumnValue + "'";
            else
                return "[" + columnInfo.ColumnName + "] = " + columnInfo.ColumnValue;
        }

        private string GetMeStatementForInsertDataInColumn(ColumnInfo columnInfo)
        {
            if (columnInfo.ColumnValueType == ColumnValueDataType.Numeric)
                return columnInfo.ColumnValue;
            else if (columnInfo.ColumnValueType == ColumnValueDataType.Text)
                return "'" + columnInfo.ColumnValue + "'";
            else
                return columnInfo.ColumnValue;
        }
    }

    class TableSchema    
    {
        public string ColumnName{get; set;}
        public string ColumnType{get; set;}
        public bool AllowDBNull{get; set;}
        public int ColumnSize{get; set;}
    }

    class ForeignKeyConstraint
    {
        public string PrimaryKeyTable { get; set; }
        public string PrimaryKeyColumn { get; set; }
        public string ForeignKeyTable { get; set; }
        public string ForeignKeyColumn { get; set; }
    }

    class DefaultKeyConstraint
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }

    class StoredProcSchema
    {
        public string BodyText { get; set; }
        private List<ProcParameter> _parameters = new List<ProcParameter>();
        public List<ProcParameter> Parameters
        {
            get { return _parameters; }
            set { _parameters = value; }
        }
    }

    class FunctionSchema
    {
        private string _type;
        public string FunctionType 
        {
            get { return _type; }
            set { _type = value; } 
        }

        public UserDefinedFunctionType FunctionUserDefinedype
        {
            get
            {
                switch (_type.Trim().ToUpper())
                {
                    case "SCALAR":
                        return UserDefinedFunctionType.Scalar;
                    case "INLINE":
                        return UserDefinedFunctionType.Inline;
                    case "TABLE":
                        return UserDefinedFunctionType.Table;
                    default:
                        return UserDefinedFunctionType.Unknown;                        
                }
            }

            private set { ;}
        }

        private string _functionReturnDataType;
        public string FunctionReturnDataType 
        {
            get 
            {
                if (string.IsNullOrEmpty(_functionReturnDataType))
                    return string.Empty;
                else
                    return _functionReturnDataType; 
            }

            set { _functionReturnDataType = value; } 
        }

        public int FunctionReturnDataTypeSize { get; set; }

        public string BodyText { get; set; }

        private List<ProcParameter> _parameters = new List<ProcParameter>();
        public List<ProcParameter> Parameters
        {
            get { return _parameters; }
            set { _parameters = value; }
        }
    }

    class ProcParameter
    {
        public string ParameterName { get; set; }
        public string ParameterType { get; set; }        
        public int ParameterSize { get; set; }
        public bool IsOutputParameter { get; set; }
    }

    class ColumnInfo
    {
        public string ColumnName { get; set; }
        public string ColumnValue { get; set; }

        private string _columnValueTypeString;
        public string ColumnValueTypeString
        {
            get{return _columnValueTypeString;}
            set { _columnValueTypeString = value; }
        }

        public ColumnValueDataType ColumnValueType
        {
            get
            {
                switch (_columnValueTypeString.Trim().ToUpper())
                {
                    case "TEXT":
                        return ColumnValueDataType.Text;
                    case "NUMERIC":
                        return ColumnValueDataType.Numeric;
                    default:
                        return ColumnValueDataType.Unknown;
                }
            }
            private set { ;}
        }
    }

    class RowInfo
    {
        private List<ColumnInfo> _rowData = new List<ColumnInfo>();
        public List<ColumnInfo> RowData
        {
            get { return _rowData; }
            set { _rowData = value; }
        }
    }

    class UpdateStatement
    {
        private List<ColumnInfo> _setStatementValue = new List<ColumnInfo>();
        public List<ColumnInfo> SetStatementValue
        {
            get { return _setStatementValue; }
            set { _setStatementValue = value; }
        }

        private List<ColumnInfo> _whereStatementValue = new List<ColumnInfo>();
        public List<ColumnInfo> WhereStatementValue
        {
            get { return _whereStatementValue; }
            set { _whereStatementValue = value; }
        }
    }

    enum ColumnValueDataType
    {
        Numeric,
        Text,
        Unknown
    }
}
